package database;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import S.po.CoursePO;
import S.po.FramePO;
import S.po.SelectPO;
import S.po.StudentPO;
import S.po.UserPO;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class courseConnector {

	final static String user = "root";
	final static String password = "000000";
	final static String url = "jdbc:mysql://127.0.0.1:3306/courses";
	final static String coursesTable = "course";
	final static String selectedCourseTable = "selectedCourse";
	final static String teacherTable = "teacher";
	
	Connection con;
	Statement stmt;
	
	public courseConnector(){
		init();
	}
		
	public void init(){
		
		String driver = "com.mysql.jdbc.Driver";
	
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			con = (Connection) DriverManager.getConnection(url, user, password);
			stmt = (Statement) con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public boolean addFrame(FramePO fp){
		String sqlstr = "insert frame (dule,category,serialNumber,type,courseName,adviceCredit,time)"
				+" value('"+fp.getModule()+"', '" +fp.getCategory()+"', '"+fp.getSN()
				+"', '"+fp.getCourseType()+"'"+"', '"+fp.getCourseName()+"'"+"', '"+fp.getCredit()+"'"
				+"', '"+fp.getTime()+"'";
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public boolean deleteFrame(FramePO fp){
		int b=0;
		String sqlstr = "delete from frame where serialNumber = '"+fp.getSN()+"'";
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public boolean updateFrame(FramePO fp){
		return deleteFrame(fp)&addFrame(fp);
		
	}
	
	public boolean addCourse(CoursePO cp){
		String sqlstr = "insert "+coursesTable+"(name,number,type,credit,time,classroom," +
				"depart,classNumber,teacher,describtion,comment) " +
				"+ value('"+cp.getCourseName()+"', '" +cp.getCourseNO()+"', '"+cp.getCourseType()
				+"', '"+cp.getCredit()+"'"+"', '"+cp.getTime()+"'"+"', '"+cp.getClassRom()+"'"
				+"', '"+cp.getDepart()+"'"+"', '"+cp.getClassNO()+"'"+"', '"+cp.getTeacher()+"'";
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public boolean updateCourse(CoursePO cp){
		return deleteCourse(cp)&addCourse(cp);
		
	}
	public boolean addScore(SelectPO sp){
		String sqlstr = "update "+selectedCourseTable+"set score = " +
				+sp.getScore()+" where courseNumber = "+sp.getCourseNO()
				+"and studentID = "+sp.getStudentId();
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public boolean addSelectedCourse(SelectPO sp){
		String sqlstr = "insert "+selectedCourseTable+"(courseNumber,couseName,studentID,studentName)"+
	"value("+sp.getCourseNO()+",'"+sp.getCourseName()+"',"+sp.getStudentId()
	+",'"+sp.getStudentName()+"'";
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public ArrayList<SelectPO> findCourse(UserPO po){
		ArrayList<SelectPO> list = new ArrayList<SelectPO>();
		String sqlstr = null;
		if(po.getRole().equals("student")){
			sqlstr = "select * from "+selectedCourseTable+" where studentID = '"+po.getId();
		}
		else
			sqlstr = "select * from "+coursesTable+" where studentID = '"+po.getId();
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				SelectPO temp = new SelectPO(rs.getString("courseNumber"),
						rs.getString("courseName"),rs.getString("studentID"),
						rs.getString("studentName"));
				list.add(temp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	public boolean delete(SelectPO sp){
		String sqlstr = "delete from "+selectedCourseTable+" where courseNumber = "
	+sp.getCourseNO()+" and studentID = "+ sp.getStudentId();
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public boolean deleteCourse(CoursePO cp){
		String sqlstr = "delete from "+coursesTable+" where courseNumber = "
	+cp.getCourseNO();
		int b=0;
		try {
			b = stmt.executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b!=0;
	}
	
	public ArrayList<CoursePO> queryCourseInfo(){
		ArrayList<CoursePO> list = new ArrayList<CoursePO>();
		String sqlstr = "select * from "+coursesTable;
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				CoursePO cp = new CoursePO(rs.getString("depart"),rs.getString("number"),
						rs.getString("name"),rs.getString("type"),rs.getInt("credit"),
						rs.getString("classNumber"),rs.getString("time"),rs.getString("teacher"));
				list.add(cp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<FramePO> queryFrame() {
		// TODO Auto-generated method stub
		ArrayList<FramePO> list = new ArrayList<FramePO>();
		String sqlstr = "select * from frame";
		try {
			ResultSet rs = stmt.executeQuery(sqlstr);
			while(rs.next()){
				FramePO fp = new FramePO("",rs.getString("module"),rs.getString("type"),rs.getString("serialNumber"),
						rs.getString("category"),new String[]{},1,rs.getString("time"));
				list.add(fp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
